package com.hushan.elevator.dao;

import com.hushan.elevator.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * @Author: zzy
 * @Date: 2024/10/22 22:21
 * @Title:
 * @Version:
 * @Package: com.hushan.elevator.dao
 */
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    List<User> findByUsername(String username);

    Optional<User> findByTelNumb(String telNumb);

    List<User> findByEmail(String email);

    Optional<User> findByUserId(Integer userId);

    List<User> findByEnable(Boolean enable);

    List<User> findByIsDeleted(Boolean isDeleted);

    List<User> findByCreatedTimeBetween(LocalDateTime start, LocalDateTime end);

    List<User> findByUpdatedTimeBetween(LocalDateTime start, LocalDateTime end);

    @Query(value = """
            SELECT  u.user_id,
                u.username,
                u.password,
                u.title,
                u.sex,
                u.tel_numb,
                u.latest_time,
                u.latest_ip,
                u.email,
                u.enable,
                u.created_by,
                u.created_time,
                u.updated_time,
                u.is_deleted FROM auth_user u \
            INNER JOIN auth_user_dept ud ON u.user_id = ud.user_id \
            WHERE ud.dept_id = :deptId and u.is_deleted = false""", nativeQuery = true)
    List<User> findUsersByDept(int deptId);

    @Query(value = "SELECT COUNT(u.user_id) FROM auth_user u " +
            "INNER JOIN auth_user_dept ud ON u.user_id = ud.user_id " +
            "WHERE ud.dept_id = :deptId and u.is_deleted = false", nativeQuery = true)
    int findCountByDept(int deptId);
}